<!DOCTYPE html>
<html lang="zh-cn">
<head>
  <title>MySQL - 为企业级框架和应用而生</title>
  <meta charset="utf-8">
  <meta name="description" content="index.description">
  <meta http-equiv="X-UA-Compatible" content="IE=Edge,chrome=1">
  <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1, user-scalable=no">
  <link rel="icon" href="/images/favicon.png" type="image/x-icon">
  <link rel="stylesheet" href="https://cdn.jsdelivr.net/docsearch.js/2/docsearch.min.css" />
<link rel="stylesheet" href="/css/index.css">

    <script>
    !function(t,e,a,r,c){t.TracertCmdCache=t.TracertCmdCache||[],t[c]=window[c]||
      {_isInit:!0,call:function(){t.TracertCmdCache.push(arguments)},
      start:function(t){this.call('start',t)}},t[c].l=new Date;
      var n=e.createElement(a),s=e.getElementsByTagName(a)[0];
      n.async=!0,n.src=r,s.parentNode.insertBefore(n,s)}
    (window,document,'script','https://tracert.alipay.com/tracert.js','Tracert');
      Tracert.start({
        plugins: [ 'BucName' ],
        spmAPos: 'a454',
        spmBPos: 'b4893',
      });
    </script>
  
</head>
<body>
  <div class="nav" >
  <header>
    <a href="/zh-cn/" class="nav-logo leftpadding" alt="egg"><img src="https://zos.alipayobjects.com/rmsportal/VTcUYAaoKqXyHJbLAPyF.svg"></a>
    <ul class="nav-item">
      <li>
        <form id="search-form">
          <input type="text" id="search-query" class="search-query st-default-search-input">
        </form>
      </li>
      <li><a href="/zh-cn/intro/" alt="指南">指南</a></li><li><a href="/api/" alt="API">API</a></li><li><a href="/zh-cn/tutorials/index.html" alt="教程">教程</a></li><li><a href="https://github.com/search?q=topic%3Aegg-plugin&type=Repositories" alt="插件">插件</a></li><li><a href="https://github.com/eggjs/egg/releases" alt="发布日志">发布日志</a></li>
      
      
        <li class="translations">
          <a class="nav-link">Translations</a>
          <span class="arrow"></span><ul id="dropdownContent" class="dropdown-content"><li><a id="en" href="/en/tutorials/mysql.html" >English</a></li><li><a id="zh-cn" href="/zh-cn/tutorials/mysql.html" style="color: #22ab28">中文</a></li></ul>
        </li>
      
      <li><iframe src="https://ghbtns.com/github-btn.html?user=eggjs&repo=egg&type=star&count=true" frameborder="0" scrolling="0" width="150px" height="20px"></iframe></li>
    </ul>
    <a id="mobileTrigger" href="#" class="mobile-trigger">
      <ul>
        <li></li>
        <li></li>
        <li></li>
      </ul>
    </a>
  </header>
</div>
  <div id="container" class="container">
    <div class="page-main">
  <article class="markdown-body">
    <h1>MySQL</h1>
    <p>在 Web 应用方面 MySQL 是最常见，最好的关系型数据库之一。非常多网站都选择 MySQL 作为网站数据库。</p>
<h2 id="egg-mysql"><a class="markdown-anchor" href="#egg-mysql">#</a> egg-mysql</h2>
<p>框架提供了 <a href="https://github.com/eggjs/egg-mysql" target="_blank" rel="noopener">egg-mysql</a> 插件来访问 MySQL 数据库。这个插件既可以访问普通的 MySQL 数据库，也可以访问基于 MySQL 协议的在线数据库服务。</p>
<h3 id="安装与配置"><a class="markdown-anchor" href="#安装与配置">#</a> 安装与配置</h3>
<p>安装对应的插件 <a href="https://github.com/eggjs/egg-mysql" target="_blank" rel="noopener">egg-mysql</a> ：</p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">$ npm i --save egg-mysql</span><br></pre></td></tr></table></figure>
<p>开启插件：</p>
<figure class="highlight js"><table><tr><td class="code"><pre><span class="line"><span class="comment">// config/plugin.js</span></span><br><span class="line">exports.mysql = &#123;</span><br><span class="line">  enable: <span class="literal">true</span>,</span><br><span class="line">  package: <span class="string">'egg-mysql'</span>,</span><br><span class="line">&#125;;</span><br></pre></td></tr></table></figure>
<p>在 <code>config/config.${env}.js</code> 配置各个环境的数据库连接信息。</p>
<h4 id="单数据源"><a class="markdown-anchor" href="#单数据源">#</a> 单数据源</h4>
<p>如果我们的应用只需要访问一个 MySQL 数据库实例，可以如下配置：</p>
<figure class="highlight js"><table><tr><td class="code"><pre><span class="line"><span class="comment">// config/config.$&#123;env&#125;.js</span></span><br><span class="line">exports.mysql = &#123;</span><br><span class="line">  <span class="comment">// 单数据库信息配置</span></span><br><span class="line">  client: &#123;</span><br><span class="line">    <span class="comment">// host</span></span><br><span class="line">    host: <span class="string">'mysql.com'</span>,</span><br><span class="line">    <span class="comment">// 端口号</span></span><br><span class="line">    port: <span class="string">'3306'</span>,</span><br><span class="line">    <span class="comment">// 用户名</span></span><br><span class="line">    user: <span class="string">'test_user'</span>,</span><br><span class="line">    <span class="comment">// 密码</span></span><br><span class="line">    password: <span class="string">'test_password'</span>,</span><br><span class="line">    <span class="comment">// 数据库名</span></span><br><span class="line">    database: <span class="string">'test'</span>,</span><br><span class="line">  &#125;,</span><br><span class="line">  <span class="comment">// 是否加载到 app 上，默认开启</span></span><br><span class="line">  app: <span class="literal">true</span>,</span><br><span class="line">  <span class="comment">// 是否加载到 agent 上，默认关闭</span></span><br><span class="line">  agent: <span class="literal">false</span>,</span><br><span class="line">&#125;;</span><br></pre></td></tr></table></figure>
<p>使用方式：</p>
<figure class="highlight js"><table><tr><td class="code"><pre><span class="line"><span class="keyword">await</span> app.mysql.query(sql, values); <span class="comment">// 单实例可以直接通过 app.mysql 访问</span></span><br></pre></td></tr></table></figure>
<h4 id="多数据源"><a class="markdown-anchor" href="#多数据源">#</a> 多数据源</h4>
<p>如果我们的应用需要访问多个 MySQL 数据源，可以按照如下配置：</p>
<figure class="highlight js"><table><tr><td class="code"><pre><span class="line">exports.mysql = &#123;</span><br><span class="line">  clients: &#123;</span><br><span class="line">    <span class="comment">// clientId, 获取client实例，需要通过 app.mysql.get('clientId') 获取</span></span><br><span class="line">    db1: &#123;</span><br><span class="line">      <span class="comment">// host</span></span><br><span class="line">      host: <span class="string">'mysql.com'</span>,</span><br><span class="line">      <span class="comment">// 端口号</span></span><br><span class="line">      port: <span class="string">'3306'</span>,</span><br><span class="line">      <span class="comment">// 用户名</span></span><br><span class="line">      user: <span class="string">'test_user'</span>,</span><br><span class="line">      <span class="comment">// 密码</span></span><br><span class="line">      password: <span class="string">'test_password'</span>,</span><br><span class="line">      <span class="comment">// 数据库名</span></span><br><span class="line">      database: <span class="string">'test'</span>,</span><br><span class="line">    &#125;,</span><br><span class="line">    db2: &#123;</span><br><span class="line">      <span class="comment">// host</span></span><br><span class="line">      host: <span class="string">'mysql2.com'</span>,</span><br><span class="line">      <span class="comment">// 端口号</span></span><br><span class="line">      port: <span class="string">'3307'</span>,</span><br><span class="line">      <span class="comment">// 用户名</span></span><br><span class="line">      user: <span class="string">'test_user'</span>,</span><br><span class="line">      <span class="comment">// 密码</span></span><br><span class="line">      password: <span class="string">'test_password'</span>,</span><br><span class="line">      <span class="comment">// 数据库名</span></span><br><span class="line">      database: <span class="string">'test'</span>,</span><br><span class="line">    &#125;,</span><br><span class="line">    <span class="comment">// ...</span></span><br><span class="line">  &#125;,</span><br><span class="line">  <span class="comment">// 所有数据库配置的默认值</span></span><br><span class="line">  <span class="keyword">default</span>: &#123;</span><br><span class="line"></span><br><span class="line">  &#125;,</span><br><span class="line"></span><br><span class="line">  <span class="comment">// 是否加载到 app 上，默认开启</span></span><br><span class="line">  app: <span class="literal">true</span>,</span><br><span class="line">  <span class="comment">// 是否加载到 agent 上，默认关闭</span></span><br><span class="line">  agent: <span class="literal">false</span>,</span><br><span class="line">&#125;;</span><br></pre></td></tr></table></figure>
<p>使用方式：</p>
<figure class="highlight js"><table><tr><td class="code"><pre><span class="line"><span class="keyword">const</span> client1 = app.mysql.get(<span class="string">'db1'</span>);</span><br><span class="line"><span class="keyword">await</span> client1.query(sql, values);</span><br><span class="line"></span><br><span class="line"><span class="keyword">const</span> client2 = app.mysql.get(<span class="string">'db2'</span>);</span><br><span class="line"><span class="keyword">await</span> client2.query(sql, values);</span><br></pre></td></tr></table></figure>
<h4 id="动态创建"><a class="markdown-anchor" href="#动态创建">#</a> 动态创建</h4>
<p>我们可以不需要将配置提前申明在配置文件中，而是在应用运行时动态的从配置中心获取实际的参数，再来初始化一个实例。</p>
<figure class="highlight js"><table><tr><td class="code"><pre><span class="line"><span class="comment">// &#123;app_root&#125;/app.js</span></span><br><span class="line"><span class="built_in">module</span>.exports = <span class="function"><span class="params">app</span> =&gt;</span> &#123;</span><br><span class="line">  app.beforeStart(<span class="keyword">async</span> () =&gt; &#123;</span><br><span class="line">    <span class="comment">// 从配置中心获取 MySQL 的配置</span></span><br><span class="line">    <span class="comment">// &#123; host: 'mysql.com', port: '3306', user: 'test_user', password: 'test_password', database: 'test' &#125;</span></span><br><span class="line">    <span class="keyword">const</span> mysqlConfig = <span class="keyword">await</span> app.configCenter.fetch(<span class="string">'mysql'</span>);</span><br><span class="line">    app.database = app.mysql.createInstance(mysqlConfig);</span><br><span class="line">  &#125;);</span><br><span class="line">&#125;;</span><br></pre></td></tr></table></figure>
<h2 id="service-层"><a class="markdown-anchor" href="#service-层">#</a> Service 层</h2>
<p>由于对 MySQL 数据库的访问操作属于 Web 层中的数据处理层，因此我们强烈建议将这部分代码放在 Service 层中维护。</p>
<p>下面是一个 Service 中访问 MySQL 数据库的例子。</p>
<p>更多 Service 层的介绍，可以参考 <a href="../basics/service.html">Service</a></p>
<figure class="highlight js"><table><tr><td class="code"><pre><span class="line"><span class="comment">// app/service/user.js</span></span><br><span class="line"><span class="class"><span class="keyword">class</span> <span class="title">UserService</span> <span class="keyword">extends</span> <span class="title">Service</span> </span>&#123;</span><br><span class="line">  <span class="keyword">async</span> find(uid) &#123;</span><br><span class="line">    <span class="comment">// 假如 我们拿到用户 id 从数据库获取用户详细信息</span></span><br><span class="line">    <span class="keyword">const</span> user = <span class="keyword">await</span> <span class="keyword">this</span>.app.mysql.get(<span class="string">'users'</span>, &#123; <span class="attr">id</span>: <span class="number">11</span> &#125;);</span><br><span class="line">    <span class="keyword">return</span> &#123; user &#125;;</span><br><span class="line">  &#125;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>
<p>之后可以通过 Controller 获取 Service 层拿到的数据。</p>
<figure class="highlight js"><table><tr><td class="code"><pre><span class="line"><span class="comment">// app/controller/user.js</span></span><br><span class="line"><span class="class"><span class="keyword">class</span> <span class="title">UserController</span> <span class="keyword">extends</span> <span class="title">Controller</span> </span>&#123;</span><br><span class="line">  <span class="keyword">async</span> info() &#123;</span><br><span class="line">    <span class="keyword">const</span> ctx = <span class="keyword">this</span>.ctx;</span><br><span class="line">    <span class="keyword">const</span> userId = ctx.params.id;</span><br><span class="line">    <span class="keyword">const</span> user = <span class="keyword">await</span> ctx.service.user.find(userId);</span><br><span class="line">    ctx.body = user;</span><br><span class="line">  &#125;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>
<h2 id="如何编写-crud-语句"><a class="markdown-anchor" href="#如何编写-crud-语句">#</a> 如何编写 CRUD 语句</h2>
<p>下面的语句若没有特殊注明，默认都书写在 <code>app/service</code> 下。</p>
<h3 id="create"><a class="markdown-anchor" href="#create">#</a> Create</h3>
<p>可以直接使用 <code>insert</code> 方法插入一条记录。</p>
<figure class="highlight js"><table><tr><td class="code"><pre><span class="line"><span class="comment">// 插入</span></span><br><span class="line"><span class="keyword">const</span> result = <span class="keyword">await</span> <span class="keyword">this</span>.app.mysql.insert(<span class="string">'posts'</span>, &#123; <span class="attr">title</span>: <span class="string">'Hello World'</span> &#125;); <span class="comment">// 在 post 表中，插入 title 为 Hello World 的记录</span></span><br><span class="line"></span><br><span class="line">=&gt; INSERT INTO <span class="string">`posts`</span>(<span class="string">`title`</span>) VALUES(<span class="string">'Hello World'</span>);</span><br><span class="line"></span><br><span class="line"><span class="built_in">console</span>.log(result);</span><br><span class="line">=&gt;</span><br><span class="line">&#123;</span><br><span class="line">  fieldCount: <span class="number">0</span>,</span><br><span class="line">  affectedRows: <span class="number">1</span>,</span><br><span class="line">  insertId: <span class="number">3710</span>,</span><br><span class="line">  serverStatus: <span class="number">2</span>,</span><br><span class="line">  warningCount: <span class="number">2</span>,</span><br><span class="line">  message: <span class="string">''</span>,</span><br><span class="line">  protocol41: <span class="literal">true</span>,</span><br><span class="line">  changedRows: <span class="number">0</span></span><br><span class="line">&#125;</span><br><span class="line"></span><br><span class="line"><span class="comment">// 判断插入成功</span></span><br><span class="line"><span class="keyword">const</span> insertSuccess = result.affectedRows === <span class="number">1</span>;</span><br></pre></td></tr></table></figure>
<h3 id="read"><a class="markdown-anchor" href="#read">#</a> Read</h3>
<p>可以直接使用 <code>get</code> 方法或 <code>select</code> 方法获取一条或多条记录。<code>select</code> 方法支持条件查询与结果的定制。</p>
<ul>
<li>查询一条记录</li>
</ul>
<figure class="highlight js"><table><tr><td class="code"><pre><span class="line"><span class="keyword">const</span> post = <span class="keyword">await</span> <span class="keyword">this</span>.app.mysql.get(<span class="string">'posts'</span>, &#123; <span class="attr">id</span>: <span class="number">12</span> &#125;);</span><br><span class="line"></span><br><span class="line">=&gt; SELECT * FROM <span class="string">`posts`</span> WHERE <span class="string">`id`</span> = <span class="number">12</span> LIMIT <span class="number">0</span>, <span class="number">1</span>;</span><br></pre></td></tr></table></figure>
<ul>
<li>查询全表</li>
</ul>
<figure class="highlight js"><table><tr><td class="code"><pre><span class="line"><span class="keyword">const</span> results = <span class="keyword">await</span> <span class="keyword">this</span>.app.mysql.select(<span class="string">'posts'</span>);</span><br><span class="line"></span><br><span class="line">=&gt; SELECT * FROM <span class="string">`posts`</span>;</span><br></pre></td></tr></table></figure>
<ul>
<li>条件查询和结果定制</li>
</ul>
<figure class="highlight js"><table><tr><td class="code"><pre><span class="line"><span class="keyword">const</span> results = <span class="keyword">await</span> <span class="keyword">this</span>.app.mysql.select(<span class="string">'posts'</span>, &#123; <span class="comment">// 搜索 post 表</span></span><br><span class="line">  where: &#123; <span class="attr">status</span>: <span class="string">'draft'</span>, <span class="attr">author</span>: [<span class="string">'author1'</span>, <span class="string">'author2'</span>] &#125;, <span class="comment">// WHERE 条件</span></span><br><span class="line">  columns: [<span class="string">'author'</span>, <span class="string">'title'</span>], <span class="comment">// 要查询的表字段</span></span><br><span class="line">  orders: [[<span class="string">'created_at'</span>,<span class="string">'desc'</span>], [<span class="string">'id'</span>,<span class="string">'desc'</span>]], <span class="comment">// 排序方式</span></span><br><span class="line">  limit: <span class="number">10</span>, <span class="comment">// 返回数据量</span></span><br><span class="line">  offset: <span class="number">0</span>, <span class="comment">// 数据偏移量</span></span><br><span class="line">&#125;);</span><br><span class="line"></span><br><span class="line">=&gt; SELECT <span class="string">`author`</span>, <span class="string">`title`</span> FROM <span class="string">`posts`</span></span><br><span class="line">  WHERE <span class="string">`status`</span> = <span class="string">'draft'</span> AND <span class="string">`author`</span> IN(<span class="string">'author1'</span>,<span class="string">'author2'</span>)</span><br><span class="line">  ORDER BY <span class="string">`created_at`</span> DESC, <span class="string">`id`</span> DESC LIMIT <span class="number">0</span>, <span class="number">10</span>;</span><br></pre></td></tr></table></figure>
<h3 id="update"><a class="markdown-anchor" href="#update">#</a> Update</h3>
<p>可以直接使用 <code>update</code> 方法更新数据库记录。</p>
<figure class="highlight js"><table><tr><td class="code"><pre><span class="line"><span class="comment">// 修改数据，将会根据主键 ID 查找，并更新</span></span><br><span class="line"><span class="keyword">const</span> row = &#123;</span><br><span class="line">  id: <span class="number">123</span>,</span><br><span class="line">  name: <span class="string">'fengmk2'</span>,</span><br><span class="line">  otherField: <span class="string">'other field value'</span>,    <span class="comment">// any other fields u want to update</span></span><br><span class="line">  modifiedAt: <span class="keyword">this</span>.app.mysql.literals.now, <span class="comment">// `now()` on db server</span></span><br><span class="line">&#125;;</span><br><span class="line"><span class="keyword">const</span> result = <span class="keyword">await</span> <span class="keyword">this</span>.app.mysql.update(<span class="string">'posts'</span>, row); <span class="comment">// 更新 posts 表中的记录</span></span><br><span class="line"></span><br><span class="line">=&gt; UPDATE <span class="string">`posts`</span> SET <span class="string">`name`</span> = <span class="string">'fengmk2'</span>, <span class="string">`modifiedAt`</span> = NOW() WHERE id = <span class="number">123</span> ;</span><br><span class="line"></span><br><span class="line"><span class="comment">// 判断更新成功</span></span><br><span class="line"><span class="keyword">const</span> updateSuccess = result.affectedRows === <span class="number">1</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">// 如果主键是自定义的 ID 名称，如 custom_id，则需要在 `where` 里面配置</span></span><br><span class="line"><span class="keyword">const</span> row = &#123;</span><br><span class="line">  name: <span class="string">'fengmk2'</span>,</span><br><span class="line">  otherField: <span class="string">'other field value'</span>,    <span class="comment">// any other fields u want to update</span></span><br><span class="line">  modifiedAt: <span class="keyword">this</span>.app.mysql.literals.now, <span class="comment">// `now()` on db server</span></span><br><span class="line">&#125;;</span><br><span class="line"></span><br><span class="line"><span class="keyword">const</span> options = &#123;</span><br><span class="line">  where: &#123;</span><br><span class="line">    custom_id: <span class="number">456</span></span><br><span class="line">  &#125;</span><br><span class="line">&#125;;</span><br><span class="line"><span class="keyword">const</span> result = <span class="keyword">await</span> <span class="keyword">this</span>.app.mysql.update(<span class="string">'posts'</span>, row, options); <span class="comment">// 更新 posts 表中的记录</span></span><br><span class="line"></span><br><span class="line">=&gt; UPDATE <span class="string">`posts`</span> SET <span class="string">`name`</span> = <span class="string">'fengmk2'</span>, <span class="string">`modifiedAt`</span> = NOW() WHERE custom_id = <span class="number">456</span> ;</span><br><span class="line"></span><br><span class="line"><span class="comment">// 判断更新成功</span></span><br><span class="line"><span class="keyword">const</span> updateSuccess = result.affectedRows === <span class="number">1</span>;</span><br></pre></td></tr></table></figure>
<h3 id="delete"><a class="markdown-anchor" href="#delete">#</a> Delete</h3>
<p>可以直接使用 <code>delete</code> 方法删除数据库记录。</p>
<figure class="highlight js"><table><tr><td class="code"><pre><span class="line"><span class="keyword">const</span> result = <span class="keyword">await</span> <span class="keyword">this</span>.app.mysql.delete(<span class="string">'posts'</span>, &#123;</span><br><span class="line">  author: <span class="string">'fengmk2'</span>,</span><br><span class="line">&#125;);</span><br><span class="line"></span><br><span class="line">=&gt; DELETE FROM <span class="string">`posts`</span> WHERE <span class="string">`author`</span> = <span class="string">'fengmk2'</span>;</span><br></pre></td></tr></table></figure>
<h2 id="直接执行-sql-语句"><a class="markdown-anchor" href="#直接执行-sql-语句">#</a> 直接执行 sql 语句</h2>
<p>插件本身也支持拼接与直接执行 sql 语句。使用 <code>query</code> 可以执行合法的 sql 语句。</p>
<p><strong>注意！！我们极其不建议开发者拼接 sql 语句，这样很容易引起 sql 注入！！</strong></p>
<p>如果必须要自己拼接 sql 语句，请使用 <code>mysql.escape</code> 方法。</p>
<p>参考 <a href="http://stackoverflow.com/questions/15778572/preventing-sql-injection-in-node-js" target="_blank" rel="noopener">preventing-sql-injection-in-node-js</a></p>
<figure class="highlight js"><table><tr><td class="code"><pre><span class="line"><span class="keyword">const</span> postId = <span class="number">1</span>;</span><br><span class="line"><span class="keyword">const</span> results = <span class="keyword">await</span> <span class="keyword">this</span>.app.mysql.query(<span class="string">'update posts set hits = (hits + ?) where id = ?'</span>, [<span class="number">1</span>, postId]);</span><br><span class="line"></span><br><span class="line">=&gt; update posts set hits = (hits + <span class="number">1</span>) where id = <span class="number">1</span>;</span><br></pre></td></tr></table></figure>
<h2 id="使用事务"><a class="markdown-anchor" href="#使用事务">#</a> 使用事务</h2>
<p>MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等。这时候使用事务处理可以方便管理这一组操作。
一个事务将一组连续的数据库操作，放在一个单一的工作单元来执行。该组内的每个单独的操作是成功，事务才能成功。如果事务中的任何操作失败，则整个事务将失败。</p>
<p>一般来说，事务是必须满足4个条件（ACID）： Atomicity（原子性）、Consistency（一致性）、Isolation（隔离性）、Durability（可靠性）</p>
<ul>
<li>原子性：确保事务内的所有操作都成功完成，否则事务将被中止在故障点，以前的操作将回滚到以前的状态。</li>
<li>一致性：对于数据库的修改是一致的。</li>
<li>隔离性：事务是彼此独立的，不互相影响</li>
<li>持久性：确保提交事务后，事务产生的结果可以永久存在。</li>
</ul>
<p>因此，对于一个事务来讲，一定伴随着 beginTransaction、commit 或 rollback，分别代表事务的开始，成功和失败回滚。</p>
<p>egg-mysql 提供了两种类型的事务。</p>
<h3 id="手动控制"><a class="markdown-anchor" href="#手动控制">#</a> 手动控制</h3>
<ul>
<li>优点：<code>beginTransaction</code>, <code>commit</code> 或 <code>rollback</code> 都由开发者来完全控制，可以做到非常细粒度的控制。</li>
<li>缺点：手写代码比较多，不是每个人都能写好。忘记了捕获异常和 cleanup 都会导致严重 bug。</li>
</ul>
<figure class="highlight js"><table><tr><td class="code"><pre><span class="line"><span class="keyword">const</span> conn = <span class="keyword">await</span> app.mysql.beginTransaction(); <span class="comment">// 初始化事务</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">try</span> &#123;</span><br><span class="line">  <span class="keyword">await</span> conn.insert(table, row1);  <span class="comment">// 第一步操作</span></span><br><span class="line">  <span class="keyword">await</span> conn.update(table, row2);  <span class="comment">// 第二步操作</span></span><br><span class="line">  <span class="keyword">await</span> conn.commit(); <span class="comment">// 提交事务</span></span><br><span class="line">&#125; <span class="keyword">catch</span> (err) &#123;</span><br><span class="line">  <span class="comment">// error, rollback</span></span><br><span class="line">  <span class="keyword">await</span> conn.rollback(); <span class="comment">// 一定记得捕获异常后回滚事务！！</span></span><br><span class="line">  <span class="keyword">throw</span> err;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>
<h3 id="自动控制transaction-with-scope"><a class="markdown-anchor" href="#自动控制transaction-with-scope">#</a> 自动控制：Transaction with scope</h3>
<ul>
<li>API：<code>beginTransactionScope(scope, ctx)</code>
<ul>
<li><code>scope</code>: 一个 generatorFunction，在这个函数里面执行这次事务的所有 sql 语句。</li>
<li><code>ctx</code>: 当前请求的上下文对象，传入 ctx 可以保证即便在出现事务嵌套的情况下，一次请求中同时只有一个激活状态的事务。</li>
</ul>
</li>
<li>优点：使用简单，不容易犯错，就感觉事务不存在的样子。</li>
<li>缺点：整个事务要么成功，要么失败，无法做细粒度控制。</li>
</ul>
<figure class="highlight js"><table><tr><td class="code"><pre><span class="line"><span class="keyword">const</span> result = <span class="keyword">await</span> app.mysql.beginTransactionScope(<span class="keyword">async</span> conn =&gt; &#123;</span><br><span class="line">  <span class="comment">// don't commit or rollback by yourself</span></span><br><span class="line">  <span class="keyword">await</span> conn.insert(table, row1);</span><br><span class="line">  <span class="keyword">await</span> conn.update(table, row2);</span><br><span class="line">  <span class="keyword">return</span> &#123; <span class="attr">success</span>: <span class="literal">true</span> &#125;;</span><br><span class="line">&#125;, ctx); <span class="comment">// ctx 是当前请求的上下文，如果是在 service 文件中，可以从 `this.ctx` 获取到</span></span><br><span class="line"><span class="comment">// if error throw on scope, will auto rollback</span></span><br></pre></td></tr></table></figure>
<h2 id="表达式literal"><a class="markdown-anchor" href="#表达式literal">#</a> 表达式(Literal)</h2>
<p>如果需要调用 MySQL 内置的函数（或表达式），可以使用 <code>Literal</code>。</p>
<h3 id="内置表达式"><a class="markdown-anchor" href="#内置表达式">#</a> 内置表达式</h3>
<ul>
<li><code>NOW()</code>：数据库当前系统时间，通过 <code>app.mysql.literals.now</code> 获取。</li>
</ul>
<figure class="highlight js"><table><tr><td class="code"><pre><span class="line"><span class="keyword">await</span> <span class="keyword">this</span>.app.mysql.insert(table, &#123;</span><br><span class="line">  create_time: <span class="keyword">this</span>.app.mysql.literals.now,</span><br><span class="line">&#125;);</span><br><span class="line"></span><br><span class="line">=&gt; INSERT INTO <span class="string">`$table`</span>(<span class="string">`create_time`</span>) VALUES(NOW())</span><br></pre></td></tr></table></figure>
<h3 id="自定义表达式"><a class="markdown-anchor" href="#自定义表达式">#</a> 自定义表达式</h3>
<p>下例展示了如何调用 MySQL 内置的 <code>CONCAT(s1, ...sn)</code> 函数，做字符串拼接。</p>
<figure class="highlight js"><table><tr><td class="code"><pre><span class="line"><span class="keyword">const</span> Literal = <span class="keyword">this</span>.app.mysql.literals.Literal;</span><br><span class="line"><span class="keyword">const</span> first = <span class="string">'James'</span>;</span><br><span class="line"><span class="keyword">const</span> last = <span class="string">'Bond'</span>;</span><br><span class="line"><span class="keyword">await</span> <span class="keyword">this</span>.app.mysql.insert(table, &#123;</span><br><span class="line">  id: <span class="number">123</span>,</span><br><span class="line">  fullname: <span class="keyword">new</span> Literal(<span class="string">`CONCAT("<span class="subst">$&#123;first&#125;</span>", "<span class="subst">$&#123;last&#125;</span>"`</span>),</span><br><span class="line">&#125;);</span><br><span class="line"></span><br><span class="line">=&gt; INSERT INTO <span class="string">`$table`</span>(<span class="string">`id`</span>, <span class="string">`fullname`</span>) VALUES(<span class="number">123</span>, CONCAT(<span class="string">"James"</span>, <span class="string">"Bond"</span>))</span><br></pre></td></tr></table></figure>

  </article>
  <aside id="mobileAside" class="toc">
  <div class="mobile-menu">
    <ul>
      <li><a href="/zh-cn/intro/" alt="指南">指南</a></li><li><a href="/api/" alt="API">API</a></li><li><a href="/zh-cn/tutorials/index.html" alt="教程">教程</a></li><li><a href="https://github.com/search?q=topic%3Aegg-plugin&type=Repositories" alt="插件">插件</a></li><li><a href="https://github.com/eggjs/egg/releases" alt="发布日志">发布日志</a></li>
      
      
        <li class="translations">
          <a class="nav-link">Translations</a>
          <span class="arrow"></span><ul id="dropdownContent" class="dropdown-content"><li><a id="en" href="/en/tutorials/mysql.html" >English</a></li><li><a id="zh-cn" href="/zh-cn/tutorials/mysql.html" style="color: #22ab28">中文</a></li></ul>
        </li>
      
    </ul>
  </div>
  <dl><dt id="title-Intro" style="cursor: pointer;" class="aside-title">新手指南<a id="collapse-icon-Intro" class="icon opend"></a></dt><dd id=panel-Intro><ul><li><a href="/zh-cn/intro/index.html" class="menu-link">Egg.js 是什么?</a></li><li><a href="/zh-cn/intro/egg-and-koa.html" class="menu-link">Egg.js 和 Koa</a></li><li><a href="/zh-cn/intro/quickstart.html" class="menu-link">快速入门</a></li><li><a href="/zh-cn/tutorials/progressive.html" class="menu-link">渐进式开发</a></li><li><a href="/zh-cn/migration.html" class="menu-link">2.x 升级指南</a></li></ul></dd><dt id="title-Basics" style="cursor: pointer;" class="aside-title">基础功能<a id="collapse-icon-Basics" class="icon opend"></a></dt><dd id=panel-Basics><ul><li><a href="/zh-cn/basics/structure.html" class="menu-link">目录结构</a></li><li><a href="/zh-cn/basics/objects.html" class="menu-link">内置对象</a></li><li><a href="/zh-cn/basics/env.html" class="menu-link">运行环境</a></li><li><a href="/zh-cn/basics/config.html" class="menu-link">配置</a></li><li><a href="/zh-cn/basics/middleware.html" class="menu-link">中间件</a></li><li><a href="/zh-cn/basics/router.html" class="menu-link">Router</a></li><li><a href="/zh-cn/basics/controller.html" class="menu-link">Controller</a></li><li><a href="/zh-cn/basics/service.html" class="menu-link">Service</a></li><li><a href="/zh-cn/basics/plugin.html" class="menu-link">插件</a></li><li><a href="/zh-cn/basics/schedule.html" class="menu-link">定时任务</a></li><li><a href="/zh-cn/basics/extend.html" class="menu-link">框架扩展</a></li><li><a href="/zh-cn/basics/app-start.html" class="menu-link">启动自定义</a></li></ul></dd><dt id="title-Core" style="cursor: pointer;" class="aside-title">核心功能<a id="collapse-icon-Core" class="icon opend"></a></dt><dd id=panel-Core><ul><li><a href="/zh-cn/core/development.html" class="menu-link">本地开发</a></li><li><a href="/zh-cn/core/unittest.html" class="menu-link">单元测试</a></li><li><a href="/zh-cn/core/deployment.html" class="menu-link">应用部署</a></li><li><a href="/zh-cn/core/logger.html" class="menu-link">日志</a></li><li><a href="/zh-cn/core/httpclient.html" class="menu-link">HttpClient</a></li><li><a href="/zh-cn/core/cookie-and-session.html" class="menu-link">Cookie and Session</a></li><li><a href="/zh-cn/core/cluster-and-ipc.html" class="menu-link">多进程模型和进程间通讯</a></li><li><a href="/zh-cn/core/view.html" class="menu-link">模板渲染</a></li><li><a href="/zh-cn/core/error-handling.html" class="menu-link">异常处理</a></li><li><a href="/zh-cn/core/security.html" class="menu-link">安全</a></li><li><a href="/zh-cn/core/i18n.html" class="menu-link">国际化</a></li></ul></dd><dt id="title-Tutorials" style="cursor: pointer;" class="aside-title">教程<a id="collapse-icon-Tutorials" class="icon opend"></a></dt><dd id=panel-Tutorials><ul><li><a href="/zh-cn/tutorials/mysql.html" class="menu-link">MySQL</a></li><li><a href="/zh-cn/tutorials/restful.html" class="menu-link">RESTful API</a></li><li><a href="/zh-cn/tutorials/passport.html" class="menu-link">Passport 鉴权</a></li><li><a href="/zh-cn/tutorials/socketio.html" class="menu-link">Socket.IO</a></li><li><a href="/zh-cn/tutorials/assets.html" class="menu-link">静态资源</a></li><li><a href="/zh-cn/tutorials/typescript.html" class="menu-link">TypeScript</a></li></ul></dd><dt id="title-Advanced" style="cursor: pointer;" class="aside-title">进阶<a id="collapse-icon-Advanced" class="icon opend"></a></dt><dd id=panel-Advanced><ul><li><a href="/zh-cn/advanced/loader.html" class="menu-link">Loader</a></li><li><a href="/zh-cn/advanced/plugin.html" class="menu-link">插件开发</a></li><li><a href="/zh-cn/advanced/framework.html" class="menu-link">框架开发</a></li><li><a href="/zh-cn/advanced/cluster-client.html" class="menu-link">多进程研发模式增强</a></li><li><a href="/zh-cn/advanced/view-plugin.html" class="menu-link">模板插件开发规范</a></li><li><a href="/zh-cn/style-guide.html" class="menu-link">代码风格指南</a></li></ul></dd><dt id="title-Community" style="cursor: pointer;" class="aside-title">社区<a id="collapse-icon-Community" class="icon opend"></a></dt><dd id=panel-Community><ul><li><a href="/zh-cn/plugins/" class="menu-link">内置插件列表</a></li><li><a href="/zh-cn/contributing.html" class="menu-link">如何贡献</a></li><li><a href="/zh-cn/resource.html" class="menu-link">资源</a></li><li><a href="/zh-cn/faq.html" class="menu-link">常见问题</a></li></ul></dd></dl>
</aside>
<script>
var mobileTrigger = document.getElementById('mobileTrigger');
var mobileAside = document.getElementById('mobileAside');

var expandMenu = function(title) {
  // handle icon
  const collapseIcon = document.getElementById('collapse-icon-' + title);
  if (collapseIcon) {
    collapseIcon.className = 'icon opend';
  }
  // handle panelEle
  const panelEle = document.getElementById('panel-' + title);
  if (panelEle) {
    panelEle.className = '';
  }
}

var collapseMenu = function(title) {
  // handle icon
  const collapseIcon = document.getElementById('collapse-icon-' + title);
  if (collapseIcon) {
    collapseIcon.className = 'icon closed';
  }
  // handle panelEle
  const panelEle = document.getElementById('panel-' + title);
  if (panelEle) {
    panelEle.className = 'aside-panel-hidden';
  }
}

mobileAside.onclick = function(e) {
  const targetId = e.target.id;
  if (targetId && (targetId.indexOf('title-') > -1 || targetId.indexOf('collapse-icon-') > -1)) {
    const title = targetId.replace('title-', '').replace('collapse-icon-', '');
    try { 
      // the the browser may have no localStroage or JSON.parse may throw exception.
      const menuInfo = JSON.parse(window.localStorage.getItem('menuInfo'));
        
      // current menu status
      const curClosed = menuInfo[title] ? menuInfo[title].closed : false; // default false

      // change UI
      curClosed ? expandMenu(title) : collapseMenu(title);

      // save menuInfo to localStorage
      menuInfo[title] = { closed: !curClosed } // opposite
      window.localStorage.setItem('menuInfo', JSON.stringify(menuInfo));
    } catch (e) {}
  }
};

mobileTrigger.onclick = function(e) {
  e.preventDefault();
  if (mobileAside.className.indexOf('mobile-show') === -1) {
    mobileAside.className += ' mobile-show';
  } else {
    mobileAside.className = 'toc';
  }
};

(function() {
  // save data to localStorage because the page will refresh when user change the url.
  let menuInfo;
  try { 
    // the the browser may have no localStroage or JSON.parse may throw exception.
    menuInfo = JSON.parse(window.localStorage.getItem('menuInfo'));
    if (!menuInfo) {
      menuInfo = {};
      window.localStorage.setItem('menuInfo', JSON.stringify(menuInfo));
    }
  } catch (e) {
    menuInfo = {}; // default {}
  }

  for (const title in menuInfo) {
    if (menuInfo[title] && menuInfo[title].closed) { // menu in closed status.
      collapseMenu(title);
    } else {
      expandMenu(title);
    }
  }

  // highlight menu
  const pathname = window.location.pathname;
  const selector = `a[href="${pathname}"].menu-link,a[href="${pathname}index.html"].menu-link`;
  const menuItem = mobileAside.querySelector(selector);
  if (menuItem) { menuItem.className += ' highlight'; }
})();
</script>

</div>

  </div>
</body>
<script src="https://cdn.jsdelivr.net/docsearch.js/2/docsearch.min.js"></script>
<script>
docsearch({
  apiKey: '1561de31a86f79507ea00cdb54ce647c',
  indexName: 'eggjs',
  inputSelector: '#search-query',
});
</script>
<div class="cnzz">
<script src="https://s11.cnzz.com/z_stat.php?id=1261142226&web_id=1261142226" language="JavaScript"></script>
</div>

</html>
